﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// 数据库操作类
/// </summary>
namespace DB
{
    public static class SQLHelper
    {
        private static string connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
        /// <summary>
        /// 连接字符串
        /// </summary>
        public static string ConnectionString 
        { 
            get { return connectionString; } 
            set 
            {
                connectionString = value;
                connection = CreateConnection();
            } 
        }

        private static IDbConnection connection;
        /// <summary>
        /// 获取或设置数据库连接对象
        /// </summary>
        public static IDbConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                return connection;
            }
            set
            {
                connection = value;
            }
        }

        /// <summary>
        /// 创建新的数据库连接对象
        /// </summary>
        /// <returns>数据库连接对象</returns>
        public static IDbConnection CreateConnection()
        {
            return CreateConnection(connectionString);
        }

        /// <summary>
        /// 创建新的数据库连接对象
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns>数据库连接对象</returns>
        public static IDbConnection CreateConnection(string connectionString)
        {
            return new SqlConnection(connectionString);
        }

        /// <summary>
        /// 带参数执行sql语句，返回第一行第一列的值
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params object[] param)
        {
            IDbCommand cmd = CreateCommand(sql, Connection, param);
            try
            {
                return cmd.ExecuteScalar();
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        /// 执行sql语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>受影响的行数</returns>
        public static int Execute(string sql)
        {
            return Execute(sql,Connection, null);
        }

        /// <summary>
        /// 带参数执行sql语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数集合</param>
        /// <returns>受影响的行数</returns>
        public static int Execute(string sql, params object[] param)
        {
            return Execute(sql, Connection, param);
        }

        /// <summary>
        /// 带参数执行sql语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="param">参数集合</param>
        /// <returns>受影响的行数</returns>
        public static int Execute(string sql, IDbConnection connection, params object[] param)
        {
            IDbCommand cmd = CreateCommand(sql, connection, param);
            try
            {
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                cmd.Dispose();
            }
        }

        /// <summary>
        /// 执行sql语句，返回结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>结果集</returns>
        public static DataSet Query(string sql)
        {
            return Query(sql,Connection,null);
        }

        /// <summary>
        /// 执行带参数sql语句，返回结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="param">参数</param>
        /// <returns>结果集</returns>
        public static DataSet Query(string sql, params object[] param)
        {
            return Query(sql, Connection, param);
        }

        /// <summary>
        /// 执行带参数sql语句，返回结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="connection">连接字符串</param>
        /// <param name="param">参数</param>
        /// <returns>结果集</returns>
        public static DataSet Query(string sql, IDbConnection connection, params object[] param)
        {
            IDbCommand cmd = CreateCommand(sql, connection, param);
            SqlDataAdapter da = new SqlDataAdapter(cmd as SqlCommand);
            try
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
            }
        }

        /// <summary>
        /// 创建并处理Command对象
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="connection">连接对象</param>
        /// <param name="param">参数</param>
        /// <returns>Command对象</returns>
        public static IDbCommand CreateCommand(string sql, IDbConnection connection, params object[] param)
        {
            IDbCommand cmd = new SqlCommand(sql, connection as SqlConnection);
            if (param != null)
            {
                for (int i = 0; i < param.Length; i++)
                {
				    if (param[i] == null)
                        param[i] = DBNull.Value;
                    cmd.Parameters.Add(new SqlParameter(i.ToString(), param[i]));
                }
            }
            return cmd;
        }

        public static void test()
        {
        }
    } 
}